const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');

// 数据库配置
const dbConfig = {
    host: '43.143.253.188',
    port: 3306,
    user: 'root', // 请根据实际情况修改
    password: '', // 请根据实际情况修改
    database: 'nextjs',
    charset: 'utf8mb4'
};

async function initCharacterDatabase() {
    let connection;

    try {
        console.log('🚀 开始初始化角色管理系统数据库...');
        console.log('📍 目标数据库:', dbConfig.host + ':' + dbConfig.port + '/' + dbConfig.database);
        console.log('');

        // 连接数据库
        connection = await mysql.createConnection(dbConfig);
        console.log('✅ 数据库连接成功');

        // 读取SQL脚本
        const sqlPath = path.join(__dirname, '../database/init-character-sequence.sql');
        const sqlContent = fs.readFileSync(sqlPath, 'utf8');

        // 分割SQL语句
        const sqlStatements = sqlContent
            .split(';')
            .map(stmt => stmt.trim())
            .filter(stmt => stmt.length > 0 && !stmt.startsWith('--'));

        console.log(`📝 找到 ${sqlStatements.length} 条SQL语句`);
        console.log('');

        // 执行SQL语句
        for (let i = 0; i < sqlStatements.length; i++) {
            const sql = sqlStatements[i];

            try {
                if (sql.trim()) {
                    await connection.execute(sql);
                    console.log(`✅ 执行SQL ${i + 1}: 成功`);
                }
            } catch (error) {
                // 忽略一些常见的错误（如表已存在等）
                if (error.code === 'ER_TABLE_EXISTS_ERROR' ||
                    error.code === 'ER_DUP_KEYNAME' ||
                    error.code === 'ER_CANT_DROP_FIELD_OR_KEY') {
                    console.log(`⚠️ 执行SQL ${i + 1}: 表或索引已存在，跳过`);
                } else {
                    console.log(`❌ 执行SQL ${i + 1}: 失败 - ${error.message}`);
                }
            }
        }

        console.log('');
        console.log('🔍 验证数据库初始化结果...');

        // 验证characters表的AUTO_INCREMENT设置
        const [rows] = await connection.execute(`
      SELECT 
        TABLE_NAME,
        AUTO_INCREMENT,
        TABLE_COMMENT
      FROM information_schema.TABLES 
      WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'characters'
    `, [dbConfig.database]);

        if (rows.length > 0) {
            const tableInfo = rows[0];
            console.log('📊 表信息:');
            console.log(`   表名: ${tableInfo.TABLE_NAME}`);
            console.log(`   自增值: ${tableInfo.AUTO_INCREMENT}`);
            console.log(`   表注释: ${tableInfo.TABLE_COMMENT}`);

            if (tableInfo.AUTO_INCREMENT >= 2001) {
                console.log('✅ AUTO_INCREMENT设置正确，从2001开始');
            } else {
                console.log('⚠️ AUTO_INCREMENT设置可能不正确');
            }
        } else {
            console.log('❌ characters表未找到');
        }

        // 检查表结构
        console.log('');
        console.log('🔍 检查表结构...');

        const tables = ['characters', 'character_attributes', 'character_detailed_attributes', 'character_skills', 'social_relations'];

        for (const tableName of tables) {
            try {
                const [columns] = await connection.execute(`DESCRIBE ${tableName}`);
                console.log(`✅ ${tableName} 表: ${columns.length} 个字段`);
            } catch (error) {
                console.log(`❌ ${tableName} 表: 检查失败 - ${error.message}`);
            }
        }

        console.log('');
        console.log('🎉 角色管理系统数据库初始化完成！');
        console.log('');
        console.log('💡 下一步操作:');
        console.log('   1. 启动NestJS应用: npm run start:dev');
        console.log('   2. 测试角色创建: node test-character-api.js');
        console.log('   3. 验证角色ID是否从2001开始');

    } catch (error) {
        console.error('❌ 数据库初始化失败:', error.message);
        console.error('   错误详情:', error);
    } finally {
        if (connection) {
            await connection.end();
            console.log('🔌 数据库连接已关闭');
        }
    }
}

// 运行初始化
if (require.main === module) {
    initCharacterDatabase();
}

module.exports = { initCharacterDatabase }; 